package com.dai.chun.dao.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.dai.chun.dao.model.Wage;
import com.dai.chun.dbc.DBManager;
import com.dai.chun.util.PageSplitUtil;

public class WageDaoImpl implements WageDao {
	private DBManager manager = DBManager.getInstance();

	public List<Wage> listWage(PageSplitUtil psu) {
		String sql = "select * from t_wage limit ?,?";
		List<Wage> wages = new ArrayList<Wage>();
		Connection conn = null;
		try {
			conn = manager.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement = manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setInt(1, psu.getStart());
			preparedStatement.setInt(2, psu.getEnd());
			ResultSet res = preparedStatement.executeQuery();

			while (res.next()) {
				Wage wage = new Wage();
				recordToPojo(wage,res);
				wages.add(wage);
			}
		} catch (Exception e) {
		} finally {
			manager.close(preparedStatement);
		}
		return wages;
	}

	public Boolean save(Wage wage) throws SQLException {
		boolean flag = false;
		String sql = "insert into t_wage (wage_emp_id,wage_emp_name,wage_subsidy_sum,wage_subsidy_detail," +
				"wage_base,wage_should_days,wage_work_days,wage_month,wage_real) value (?,?,?,?,?,?,?,?,?)";
		Connection conn = manager.getConnection();
		PreparedStatement preparedStatement = manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setInt( 1,wage.getWage_emp_id());
			preparedStatement.setString( 2,wage.getWage_emp_name());
			preparedStatement.setFloat( 3,wage.getWage_subsidy_sum());
			preparedStatement.setString( 4,wage.getWage_subsidy_detail());
			preparedStatement.setFloat( 5,wage.getWage_base());
			preparedStatement.setFloat( 6,wage.getWage_should_days());
			preparedStatement.setFloat( 7,wage.getWage_work_days());
			preparedStatement.setString( 8,wage.getWage_month());
			preparedStatement.setFloat( 9,wage.getWage_real());
			int count = preparedStatement.executeUpdate(); //
			if (count > 0) {
				flag = true;
			}
		} catch (Exception e) {
		} finally {
			manager.close(preparedStatement);
		}
		return flag;
	}

	@Override
	public int findAllCount() throws SQLException {
		int ret = 0;
		String sql = "select count(*) from t_wage";
		Connection conn = manager.getConnection();
		java.sql.Statement sm = conn.createStatement();
		ResultSet rs = sm.executeQuery(sql);
		if (rs.next()) {
			ret = rs.getInt(1);
		}
		manager.close(sm);
		return ret;
	}

	@Override
	public int delWage(int wage_id) throws SQLException {
		int ret = 0;
		String sql = "delete from t_wage where wage_id=?";
		Connection conn = manager.getConnection();
		PreparedStatement preparedStatement = manager.createPRPstmt(conn, sql);
		preparedStatement.setInt(1, wage_id);
		ret = preparedStatement.executeUpdate();
		manager.close(preparedStatement);
		return ret;

	}

	@Override
	public int updateWage(Wage wage) throws SQLException {
		int ret = 0;
		String sql = "update t_wage set wage_name=? where wage_id=?";
		Connection conn = manager.getConnection();
		PreparedStatement preparedStatement = manager.createPRPstmt(conn, sql);
		try {
			// preparedStatement.setString( 1,wage.getWage_name());
			preparedStatement.setInt(2, wage.getWage_id());
			int count = preparedStatement.executeUpdate(); //
			if (count > 0) {
				ret = 1;
			}
		} catch (Exception e) {
		} finally {
			manager.close(preparedStatement);
		}

		return ret;
	}

	@Override
	public Wage findWageById(int wage_id) {
		String sql = "select * from t_wage where wage_id=?";
		Wage wage = new Wage();
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = manager.getConnection();
			preparedStatement = manager.createPRPstmt(conn, sql);
			preparedStatement.setInt(1, wage_id);
			ResultSet res = preparedStatement.executeQuery();
			while (res.next()) {
				recordToPojo(wage,res);
			}
		} catch (Exception e) {
		} finally {
			manager.close(preparedStatement);
		}
		return wage;
	}

	@Override
	public List<Wage> listWageByDeptId(PageSplitUtil psu, int emp_id) {
		String sql = "select * from t_wage  where wage_emp_id=? limit ?,?";
		List<Wage> wages = new ArrayList<Wage>();
		Connection conn = null;
		try {
			conn = manager.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement = manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setInt(1, emp_id);
			preparedStatement.setInt(2, psu.getStart());
			preparedStatement.setInt(3, psu.getEnd());
			ResultSet res = preparedStatement.executeQuery();

			while (res.next()) {
				Wage wage = new Wage();
				recordToPojo(wage,res);
				wages.add(wage);
			}
		} catch (Exception e) {
		} finally {
			manager.close(preparedStatement);
		}
		return wages;
	}

	@Override
	public List<Wage> searchWage(String sql) {
		List<Wage> wages = new ArrayList<Wage>();
		Connection conn = null;
		try {
			conn = manager.getConnection();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement = manager.createPRPstmt(conn, sql);
		try {
			ResultSet res = preparedStatement.executeQuery();

			while (res.next()) {
				Wage wage = new Wage();
				recordToPojo(wage,res);
				wages.add(wage);
			}
		} catch (Exception e) {
		} finally {
			manager.close(preparedStatement);
		}
		return wages;
	}

	@Override
	public List<Wage> listWageByMonth(Wage w) {
		String sql = "select * from t_wage  where wage_month= ?";
		List<Wage> wages = new ArrayList<Wage>();
		Connection conn = null;
		try {
			conn = manager.getConnection();
		} catch (SQLException e1) {
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement = manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setString(1, w.getWage_month());
			ResultSet res = preparedStatement.executeQuery();

			while (res.next()) {
				Wage wage = new Wage();
				recordToPojo(wage,res);
				
				wages.add(wage);
			}
		} catch (Exception e) {
		} finally {
			manager.close(preparedStatement);
		}
		return wages;
	}

	private void recordToPojo(Wage wage, ResultSet res) throws SQLException {
		wage.setWage_id(res.getInt("wage_id"));
		wage.setWage_emp_id(res.getInt("wage_emp_id"));
		wage.setWage_emp_num(res.getString("wage_emp_num"));
		wage.setWage_emp_name(res.getString("wage_emp_name"));
		wage.setWage_emp_dept(res.getString("wage_emp_dept"));
		wage.setWage_subsidy_sum(res.getFloat("wage_subsidy_sum"));
		wage.setWage_subsidy_detail(res.getString("wage_subsidy_detail"));
		wage.setWage_base(res.getFloat("wage_base"));
		wage.setWage_sxyj(res.getFloat("wage_sxyj"));
		wage.setWage_should(res.getFloat("wage_should"));
		wage.setWage_month(res.getString("wage_month"));
		wage.setWage_real(res.getFloat("wage_real"));
		wage.setWage_tax(res.getFloat("wage_tax"));
		
	}

	@Override
	public void moveWageTable(String wage_month) {
		Connection conn = null;
		Statement statement=null;
		try {
			conn = manager.getConnection();
			statement=conn.createStatement();
			statement.execute("insert into t_wage_backup select * from t_wage where " +
					"t_wage.wage_month='"+wage_month+"'");
			statement.execute("delete * from t_wage where wage_month='"+wage_month+"'");
		} catch (SQLException e1) {
			e1.printStackTrace();
		}finally {
			manager.close(statement);
		}
		
	}

	@Override
	public void save(List<Wage> wageList) throws SQLException {
	
		String sql = "insert into t_wage (wage_emp_id,wage_emp_num,wage_emp_name,wage_emp_dept,wage_subsidy_sum,wage_subsidy_detail," +
				"wage_base,wage_should_days,wage_work_days,wage_month,wage_real,wage_dept_id,wage_tax,wage_sxyj,wage_should) value (?,?,?,?,?,?,?,?,?)";
		Connection conn = manager.getConnection();
		PreparedStatement preparedStatement = manager.createPRPstmt(conn, sql);
		try {
			for(Wage wage:wageList){
				preparedStatement.setInt( 1,wage.getWage_emp_id());
				preparedStatement.setString( 2,wage.getWage_emp_num());
				preparedStatement.setString( 3,wage.getWage_emp_name());
				preparedStatement.setString( 4,wage.getWage_emp_dept());
				preparedStatement.setFloat( 5,wage.getWage_subsidy_sum());
				preparedStatement.setString( 6,wage.getWage_subsidy_detail());
				preparedStatement.setFloat( 7,wage.getWage_penalty());
				preparedStatement.setFloat( 8,wage.getWage_reward());
				preparedStatement.setFloat(9,wage.getWage_base());
				preparedStatement.setFloat( 10,wage.getWage_should_days());
				preparedStatement.setFloat( 11,wage.getWage_work_days());
				preparedStatement.setString( 12,wage.getWage_month());
				preparedStatement.setFloat( 13,wage.getWage_real());
				preparedStatement.setFloat( 14,wage.getWage_real());
				preparedStatement.setInt( 15,wage.getWage_dept_id());
				preparedStatement.setFloat( 16,wage.getWage_tax());
				preparedStatement.setFloat( 17,wage.getWage_sxyj());
				preparedStatement.setFloat( 18,wage.getWage_should());
				preparedStatement.executeUpdate(); //
			}
		} catch (Exception e) {
		} finally {
			manager.close(preparedStatement);
		}
		
	}


}
